﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using TRPOP.CRM.Component.Model;
using TRPOP.Common;

namespace TRPOP.CRM.Component.DALSQLServer
{
    public class CompanyRemindDAL
    {

        private static TRPOP.ComSqlDAL.ICommonDAL CommonDAL = new TRPOP.ComSqlDAL.CommonDAL();
        #region 私有的方法
        ///	<summary>
        ///	从Table取数据到实体
        ///	</summary>
        ///	<param name="oDataRow">数据行</param>
        ///	<returns>返回实体</returns>
        private static CompanyRemindInfo ParseRowTomodel(DataRow oDataRow)
        {
            CompanyRemindInfo oInfo = new CompanyRemindInfo();
            oInfo.iEmployeeID = oDataRow["iEmployeeID"] is DBNull ? 0 : (int)oDataRow["iEmployeeID"];
            oInfo.iCompanyID = oDataRow["iCompanyID"] is DBNull ? 0 : (int)oDataRow["iCompanyID"];
            oInfo.bIsEnable = oDataRow["bIsEnable"] is DBNull ? false : (bool)oDataRow["bIsEnable"];
            oInfo.dtCreationTime = oDataRow["dtCreationTime"] is DBNull ? (new DateTime(1900, 1, 1)) : (DateTime)oDataRow["dtCreationTime"];
            oInfo.dtStartTime = oDataRow["dtStartTime"] is DBNull ? (new DateTime(1900, 1, 1)) : (DateTime)oDataRow["dtStartTime"];
            oInfo.dtEndTime = oDataRow["dtEndTime"] is DBNull ? (new DateTime(1900, 1, 1)) : (DateTime)oDataRow["dtEndTime"];
            oInfo.iRemindCategoryID = oDataRow["iRemindCategoryID"] is DBNull ? 0 : (int)oDataRow["iRemindCategoryID"];
            oInfo.iCompanyRemindID = oDataRow["iCompanyRemindID"] is DBNull ? 0 : (int)oDataRow["iCompanyRemindID"];
            return oInfo;
        }


        ///	<summary>
        ///	通用的添加存储过程的变量
        ///	</summary>
        ///	<param name="osdDataBase">SqlDatabase</param>
        ///	<param name="odbCommand">DbCommand</param>
        ///	<param name="oInfo">实体</param>
        private static CompanyRemindInfo ParseModeltoParam(SqlDatabase odbDataBase, DbCommand odbCommand, CompanyRemindInfo oInfo)
        {
            odbDataBase.AddInParameter(odbCommand, "@iEmployeeID", System.Data.DbType.Int32, oInfo.iEmployeeID);
            odbDataBase.AddInParameter(odbCommand, "@iCompanyID", System.Data.DbType.Int32, oInfo.iCompanyID);
            odbDataBase.AddInParameter(odbCommand, "@bIsEnable", System.Data.DbType.Boolean, oInfo.bIsEnable);
            odbDataBase.AddInParameter(odbCommand, "@dtCreationTime", System.Data.DbType.DateTime, oInfo.dtCreationTime);
            odbDataBase.AddInParameter(odbCommand, "@dtStartTime", System.Data.DbType.DateTime, oInfo.dtStartTime);
            odbDataBase.AddInParameter(odbCommand, "@dtEndTime", System.Data.DbType.DateTime, oInfo.dtEndTime);
            odbDataBase.AddInParameter(odbCommand, "@iRemindCategoryID", System.Data.DbType.Int32, oInfo.iRemindCategoryID);
            odbDataBase.AddParameter(odbCommand, "@iCompanyRemindID", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, oInfo.iCompanyRemindID);
            return oInfo;
        }


        #endregion

        ///	<summary>
        ///	 更新或添加
        ///	</summary>
        ///	<param name="oInfo"></param>
        ///	<returns>bool</returns>
        public bool Save(CompanyRemindInfo oInfo)
        {
            bool flag = false;
            try
            {
                SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DbCommand odbCommand = odbDataBase.GetStoredProcCommand("CompanyRemind_Save");
                ParseModeltoParam(odbDataBase, odbCommand, oInfo);
                odbDataBase.ExecuteNonQuery(odbCommand);
                oInfo.iCompanyRemindID = (int)odbDataBase.GetParameterValue(odbCommand, "@iCompanyRemindID");
                flag = true;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return flag;
        }

        ///	<summary>
        ///	根据Primary Key获取信息实体
        ///	</summary>
        ///	<param name="intiCompanyRemindID"></param>
        ///	<returns>CompanyRemindInfo</returns>
        public CompanyRemindInfo GetInfoById(int intiCompanyRemindID)
        {
            CompanyRemindInfo oInfo = null;
            string strSqlString = "SELECT * FROM CompanyRemind WHERE  iCompanyRemindID=" + intiCompanyRemindID + "    ";
            try
            {
                DataSet oDataSet = CommonDAL.GetDataSet(strSqlString, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                if (oDataTable.Rows.Count == 1)
                {
                    oInfo = ParseRowTomodel(oDataTable.Rows[0]);
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }

        /// <summary>
        /// 删除 工作计划
        /// </summary>
        /// <param name="strCondation"></param>
        /// <returns></returns>
        public bool DeleteInfoById(string strCondation)
        {
            string strSqlString = "   UPDATE [CompanyRemind] SET [bIsEnable]=0 WHERE [dtEndTime]<=GETDATE()    " + strCondation;
            try
            {
                return CommonDAL.ExeSQLQuery(strSqlString, SystemInfoHelper.getSystemInfo().DataBaseConnection) == EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
        }

        ///	<summary>
        ///	 获取信息列表（无分页）
        ///	</summary>
        ///	<param name="intTopn">前N条，为0时表示所有符合条件的</param>
        ///	<param name="strCondition">自定义条件，要以And开头</param>
        ///	<param name="strOrderby">排序，不需要Order by </param>
        ///	<returns>List<CompanyRemindInfo></returns>
        public List<CompanyRemindInfo> GetListByCondition(int intTopn, string strCondition, string strOrderby)
        {
            List<CompanyRemindInfo> oList = new List<CompanyRemindInfo>();
            string strSql = CommonDAL.strbuildSqlForList(intTopn, strCondition, strOrderby, "CompanyRemind");
            try
            {
                DataSet oDataSet = CommonDAL.GetDataSet(strSql, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                foreach (DataRow otempRow in oDataTable.Rows)
                {
                    oList.Add(ParseRowTomodel(otempRow));
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oList;
        }

        ///	<summary>
        ///	获取分页信息
        ///	</summary>
        /// <param name="intPageNumber">当前页页码</param>
        /// <param name="intPageSize">分页大小</param>
        /// <param name="strCondition">查询条件 and ....</param>
        /// <param name="strOrderby">排序的字段名 id desc</param>
        /// <param name="intRecordCount">总记录数</param>
        ///	<returns>List<CompanyRemindInfo></returns>
        public List<CompanyRemindInfo> GetListByPager(int intPageNumber, int intPageSize, string strCondition, string strOrderby, out int intRecordCount)
        {
            List<CompanyRemindInfo> oList = new List<CompanyRemindInfo>();
            try
            {
                DataSet oDataSet = CommonDAL.GetListByPager("CompanyRemind", "*", intPageNumber, intPageSize, strCondition, strOrderby, out  intRecordCount, SystemInfoHelper.getSystemInfo().DataBaseConnection);
                DataTable oDataTable = oDataSet.Tables[0];
                foreach (DataRow otempRow in oDataTable.Rows)
                {
                    oList.Add(ParseRowTomodel(otempRow));
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oList;
        }

        /// <summary>
        /// 添加 个人工作计划
        /// </summary>
        /// <param name="strCompanyIDs"></param>
        /// <param name="oCompanyRemindInfo"></param>
        /// <returns></returns>
        public bool SaveRemindCompany(string strCompanyIDs, CompanyRemindInfo oCompanyRemindInfo)
        {
            bool flag = false;
            DbCommand odbCommand = null;

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().DataBaseConnection);
            DbConnection odbConnection = odbDataBase.CreateConnection();
            odbConnection.Open();

            DbTransaction odbTransaction = odbConnection.BeginTransaction();
            try
            {
                foreach (string strCompanyID in strCompanyIDs.Split(','))
                {

                    if (odbCommand != null)
                    {
                        odbCommand.Parameters.Clear();
                    }

                    oCompanyRemindInfo.iCompanyID = strCompanyID.ToInt();

                    if (oCompanyRemindInfo.iCompanyID <= 0)
                        continue;

                    odbCommand = odbDataBase.GetStoredProcCommand("CompanyRemind_Save");

                    ParseModeltoParam(odbDataBase, odbCommand, oCompanyRemindInfo);

                    odbDataBase.ExecuteNonQuery(odbCommand, odbTransaction);
                }

                //提交事物
                odbTransaction.Commit();
                flag = true;
            }
            catch (Exception oeException)
            {
                //事物回滚
                odbTransaction.Rollback();

                throw new Exception(oeException.Message, oeException);
            }
            finally
            {
                odbConnection.Close();
            }

            return flag;
        }
    }
}
